package scream.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
//import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
//import java.util.Collection;

import scream.excepcion.DAOExcepcion;
import scream.modelo.Representante;
import scream.util.ConexionBD;
import scream.dao.BaseDAO;

public class RepresentanteDAO extends BaseDAO {

	public void insertar(Representante repre) throws DAOExcepcion {
		System.out.println("RepresentanteDAO: insertar(Representante repre)");
		String query = "INSERT INTO Representante(coRepresentante, noTipoDocumento,nuDocumento,"
				+ "noApellidoPaterno,noApellidoMaterno,noNombres,noCargo,coCliente,coGrupo)"
				+ " VALUES (?,?,?,?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;

		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, repre.getCoRepresentante());
			stmt.setString(2, repre.getNoTipoDocumento());
			stmt.setString(3, repre.getNuNumeroDocumento());
			stmt.setString(4, repre.getNoApellidoPaterno());
			stmt.setString(5, repre.getNoApellidoMaterno());
			stmt.setString(6, repre.getNoNombres());
			stmt.setString(7, repre.getNoCargo());
			stmt.setString(8, repre.getCliente_coCliente());
			stmt.setString(9, repre.getGrupo_coGrupo());

			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}

	public Collection<Representante> Buscar_Repre_x_Cliente(String Cod_Cliente)
			throws DAOExcepcion {
		Collection<Representante> lista = new ArrayList<Representante>();

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			String query = "SELECT coRepresentante,noNombres,noApellidoPaterno,noApellidoMaterno"
					+ " from Representante where coCliente = ?";
			if (Cod_Cliente == "") {
				query = "SELECT coRepresentante,noNombres,noApellidoPaterno,noApellidoMaterno"
						+ " from Representante ";
			}

			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			if (Cod_Cliente != "") {
				stmt.setString(1, Cod_Cliente);
			}
			rs = stmt.executeQuery();

			while (rs.next()) {

				Representante r = new Representante();
				r.setCoRepresentante(rs.getString("coRepresentante"));
				r.setNoNombres(rs.getString("noNombres"));
				r.setNoApellidoPaterno(rs.getString("noApellidoPaterno"));
				r.setNoApellidoMaterno(rs.getString("noApellidoMaterno"));
				lista.add(r);
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}

		return lista;
	}

	public boolean Existe_Repre(String tipo, String numero) throws DAOExcepcion {
		boolean respuesta = false;

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			String query = "SELECT * from Representante where noTipoDocumento = ? and nuDocumento = ? ";

			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);

			stmt.setString(1, tipo);
			stmt.setString(2, numero);

			rs = stmt.executeQuery();

			if (rs.next()) {
				respuesta = true;
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}

		return respuesta;
	}

}